from sqlalchemy import Column, Integer, String
from backend.library.database import Base
from pydantic import BaseModel, Field
from typing import List, Union
from backend.library.utils import md5

class SysUser(Base):

    __tablename__ = 'user'

    id = Column(Integer, primary_key=True, autoincrement=True, comment="ID" )
    role = Column(Integer, comment="角色：1 管理员 2 教师 3 学生")
    username = Column(String(50), comment="登录名（学号、工号）")
    realname = Column(String(50), comment="姓名")
    password = Column(String(100), comment="登陆密码（md5加密 123123 4297f44b13955235245b2497399d7a93）")
    salt = Column(String(8), comment="密码盐 salt, 四位随机数")
    grade = Column(Integer, default=2022, comment="年纪")
    class_name = Column(String(100), comment="班级名称")
    status =  Column(Integer, comment="状态 0 禁用 1 启用") 
    pwd_question = Column(String(100), comment="密码问题")
    pwd_answer = Column(String(100), comment="密码答案")
    token = Column(String(100), comment="登录凭证")
    login_failure = Column(Integer, default=0, comment="密码连续输入错误次数") 
    prevent_time = Column(Integer, comment="阻止登陆时间")


class LoginInfo(BaseModel):
    username: str = Field(pattern="^\w{5,20}$")
    password: str = Field(pattern="^\w{5,20}$")

class NewUserInfo(BaseModel):
    username: str = Field(pattern="^\w{5,20}$")
    realname: str = Field(min_length=2)
    grade: Union[int|None] = Field(default=0)
    class_name: Union[str|None] = Field(default="")
    status: int = Field(default=1)
    role:int=Field(default=3)

class UpdateUserInfo(BaseModel):
    id:int
    username: str = Field(pattern="^\w{5,20}$")
    realname: str = Field(min_length=2)
    grade: Union[int|None] = Field(default=0)
    class_name: Union[str|None] = Field(default="")
    status: int = Field(default=1)
    role:int=Field(default=3)

class UserIds(BaseModel):
    ids: List[int]

class ResetPasword(BaseModel):
    user_id:int
    oldpassword: str = Field(pattern="^\w{5,20}$")
    newpassword: str = Field(pattern="^\w{5,20}$")
    newpassword2: str = Field(pattern="^\w{5,20}$")

class ChangStatus(BaseModel):
    user_id:int
    status: int



# 用户模型类
# 封装了用户的基本数据库操作
class UserModel:
    
    @classmethod
    def get_user_by_id(cls, db, user_id: int):
        """
        通过用户ID获取用户信息
        @params db 数据库session
        @params username  用户姓名
        @return user | None
        """
        try:
            user = db.query(SysUser).filter(SysUser.id==user_id).first()
            return user
        except Exception as e:
            print("Error-----------<>", str(e))
            return None

    @classmethod
    def get_user_by_username(cls, db, username: str):
        """
        通过用户名获取用户信息
        @params db 数据库session
        @params username  用户姓名
        @return user | None
        """
        try:
            user = db.query(SysUser).filter(SysUser.username==username).first()
            print("=============>", user)
            return user
        except Exception as e:
            print("Error-----------<>", str(e))
            return None

    @classmethod
    def update_user(cls, db, user_id:int, data:dict):
        """
        更新用户信息
        @params user_id 用户ID
        @params data  待修改用户信息，字典类型
        @return bool
        """
        try:
            user = db.query(SysUser).filter(SysUser.id==user_id).update(data)
            db.commit()
            return True
        except Exception as e:
            return False
        
    @classmethod
    def get_user_by_token(cls,  db,  token: str ):
        """
        通过token、获取用户信息
        @params db 数据库session
        @params token  请求发送过来的token
        @return User | None
        """
        try:
            user = db.query(SysUser).filter(SysUser.token==token).first()
            return user
        except Exception as e:
            print("Error-----------<>", str(e))
            return None
        
    @classmethod
    def add_user(cls, db, data:dict):
        """
        添加新用户
        @params db 数据库session
        @params data  待添加的用户信息，字典类型
        @return bool
        """
        try:
            if data.get('password',None)==None:
                data['password'] = md5("123456")
            user = SysUser(**data)
            db.add(user)
            db.commit()
            return True
        except Exception as e:
            print(str(e))
            return False
   
    @classmethod
    def del_user(cls, db, ids:list):
        """
        删除用户
        @params db 数据库session
        @params data  待添加的用户信息，字典类型
        @return bool
        """
        try:
            for id in ids:
                db.query(SysUser).filter(SysUser.id==id).delete()
                db.commit()
            # db.query(SysUser).filter(SysUser.id.in_(ids)).delete()
            # db.commit()
            return True
        except Exception as e:
            return False

    @classmethod
    def search_user(cls, db, realname:str='', role:int=0, page:int = 1, limit:int = 10 ):
        """
        查询用户
        """
        offset = (page-1)*limit
        
        countQuery = db.query(SysUser)
        listQuery = db.query(SysUser)

        if realname!='':
            countQuery = countQuery.filter(SysUser.realname==realname)
            listQuery = listQuery.filter(SysUser.realname==realname)

        if role>0:
            countQuery = countQuery.filter(SysUser.role==role)
            listQuery = listQuery.filter(SysUser.role==role)

        total = countQuery.count()
        res = listQuery.offset(offset).limit(limit).all()

        return total,res
        







class Signin(Base):

    __tablename__ = 'signin'

    id = Column(Integer, primary_key=True, autoincrement=True, comment="ID" )
    
    location = Column(String(100), comment="上课地点")
    course_name = Column(String(200), comment="课程名称, course表冗余字段") 
    teacher_id = Column(Integer,  comment="任课教师, course表冗余字段")
    teacher_name = Column(String(100), comment="教师姓名, course表冗余字段")
    class_name = Column(String(100), comment="上课班级, course表冗余字段")
    term = Column(String(100), comment="开设学期, course表冗余字段")
    lng = Column(String(20), comment="打卡经度")
    lat = Column(String(20), comment="打卡纬度")
    status = Column(Integer,  comment="签到状态 0 未打开，1 正常，2 异常，3 迟到，4 补卡")
